*********************************
* Setup for VOCAL 
*
* 03/12/2024
*********************************

local files: dir "$raw/VOCAL/" files "*.sav"
foreach f in `files'{
	local filename = substr("`f'",1,4)
	import spss using "$raw/VOCAL/`f'", clear
	save "${raw}/VOCAL/`filename'.dta",replace
}

use "$raw/VOCAL/2018.dta",replace

foreach i in 2019 2021 2022{
	append using "$raw/VOCAL/`i'.dta"
}


rename *, lower
drop _v1 studentinc

encode dimension, gen(dimension_code)
drop dimension
rename dimension_code dimension


gduplicates tag yr org4code org8code grade studgrp dimension index_avg,gen(dup)
bys yr org4code org8code grade studgrp dimension index_avg: gen num = _n if dup>0
replace num = 1 if num == .
keep if num == 1
drop dup num

greshape wide index_avg, by(yr org4code org8code grade studgrp) key(dimension)

rename index_avg1 safety_bull
rename index_avg2 engagement
rename index_avg3 environment
rename index_avg4 environ_inst
rename index_avg5 overall
rename index_avg6 engage_part
rename index_avg7 safety

drop environ_inst safety_bull engage_part


global subgroup AFAM ASIAN HISP WH OTHER F M NB ELL NOTELL LOW_INC NOTLOW_INC SWD NOT_SWD ECODIS NOT_ECODIS
global dimen overall engage environ safety
global grade 5 8 10
foreach i in $subgroup {
	gen overall_`i' = .
	gen engage_`i' = .
	gen environ_`i' = .
	gen safety_`i' = .
	replace overall_`i' = overall if studgrp == "`i'" & grade == 0
	replace engage_`i' = engagement if studgrp == "`i'" & grade == 0
	replace environ_`i' = environment if studgrp == "`i'" & grade == 0
	replace safety_`i' = safety if studgrp == "`i'" & grade == 0
	foreach var in $dimen{
		bys yr org4code org8code grade: egen max_`var'_`i' = max(`var'_`i') 
		replace `var'_`i' = max_`var'_`i' if missing(`var'_`i')
		drop max_`var'_`i'
	}
}

keep if studgrp == "ALL"
drop studgrp

foreach i in $grade{
	gen overall_`i' = .
	gen engage_`i' = .
	gen environ_`i' = .
	gen safety_`i' = .
	replace overall_`i' = overall if grade == `i'
	replace engage_`i' = engagement if grade == `i'
	replace environ_`i' = environment if grade == `i'
	replace safety_`i' = safety if grade == `i'
	foreach var in $dimen{
		bys yr org4code org8code: egen max_`var'_`i' = max(`var'_`i') 
		replace `var'_`i' = max_`var'_`i' if missing(`var'_`i')
		drop max_`var'_`i'
	}
}

keep if grade == 0
drop grade

replace org4code = org4code * 10000
drop if org8code == 0
rename org4code discode

save "$saves/VOCAL18-22.dta",replace

use "$raw/VOCAL/2023.dta",replace

rename *, lower
drop *_cnt
drop bullying* instruction* participation*
rename (schoolclimate_avg engagement_avg safety_avg environment_avg) (overall engagement environment safety)

foreach i in $subgroup {
	gen overall_`i' = .
	gen engage_`i' = .
	gen environ_`i' = .
	gen safety_`i' = .
	replace overall_`i' = overall if studgrp == "`i'" & grade == 0
	replace engage_`i' = engagement if studgrp == "`i'" & grade == 0
	replace environ_`i' = environment if studgrp == "`i'" & grade == 0
	replace safety_`i' = safety if studgrp == "`i'" & grade == 0
	foreach var in $dimen{
		bys yr discode org8code grade: egen max_`var'_`i' = max(`var'_`i') 
		replace `var'_`i' = max_`var'_`i' if missing(`var'_`i')
		drop max_`var'_`i'
	}
}

keep if studgrp == "ALL"
drop studgrp

foreach i in $grade{
	gen overall_`i' = .
	gen engage_`i' = .
	gen environ_`i' = .
	gen safety_`i' = .
	replace overall_`i' = overall if grade == `i'
	replace engage_`i' = engagement if grade == `i'
	replace environ_`i' = environment if grade == `i'
	replace safety_`i' = safety if grade == `i'
	foreach var in $dimen{
		bys yr discode org8code: egen max_`var'_`i' = max(`var'_`i') 
		replace `var'_`i' = max_`var'_`i' if missing(`var'_`i')
		drop max_`var'_`i'
	}
}

keep if grade == 0
drop grade 

drop districtname schoolname

append using "$saves/VOCAL18-22.dta"

sort discode org8code yr

drop if overall == .

label variable yr "Survey Year"
label variable discode "District Code"
label variable org8code "School Code"
label variable overall "School Climate"
label variable engagement "Engagement"
label variable environment "Environment"
label variable safety "Safety"
label variable overall_AFAM "School Climate - African American"
label variable engage_AFAM "Engagement - African American"
label variable environ_AFAM "Environment - African American"
label variable safety_AFAM "Safety - African American"
label variable overall_ASIAN "School Climate - Asian"
label variable engage_ASIAN "Engagement - Asian"
label variable environ_ASIAN "Environment - Asian"
label variable safety_ASIAN "Safety - Asian"
label variable overall_HISP "School Climate - Hispanic"
label variable engage_HISP "Engagement - Hispanic"
label variable environ_HISP "Environment - Hispanic"
label variable safety_HISP "Safety - Hispanic"
label variable overall_WH "School Climate - White"
label variable engage_WH "Engagement - White"
label variable environ_WH "Environment - White"
label variable safety_WH "Safety - White"
label variable overall_OTHER "School Climate - Other Race"
label variable engage_OTHER "Engagement - Other Race"
label variable environ_OTHER "Environment - Other Race"
label variable safety_OTHER "Safety - Other Race"
label variable overall_F "School Climate - Female"
label variable engage_F "Engagement - Female"
label variable environ_F "Environment - Female"
label variable safety_F "Safety - Female"
label variable overall_M "School Climate - Male"
label variable engage_M "Engagement - Male"
label variable environ_M "Environment - Male"
label variable safety_M "Safety - Male"
label variable overall_NB "School Climate - Non Binary"
label variable engage_NB "Engagement - Non Binary"
label variable environ_NB "Environment - Non Binary"
label variable safety_NB "Safety - Non Binary"
label variable overall_ELL "School Climate - English Language Learner"
label variable engage_ELL "Engagement - English Language Learner"
label variable environ_ELL "Environment - English Language Learner"
label variable safety_ELL "Safety - English Language Learner"
label variable overall_NOTELL "School Climate - Not English Language Learner"
label variable engage_NOTELL "Engagement - Not English Language Learner"
label variable environ_NOTELL "Environment - Not English Language Learner"
label variable safety_NOTELL "Safety - Not English Language Learner"
label variable overall_LOW_INC "School Climate - Low Income Students"
label variable engage_LOW_INC "Engagement - Low Income Students"
label variable environ_LOW_INC "Environment - Low Income Students"
label variable safety_LOW_INC "Safety - Low Income Students"
label variable overall_NOTLOW_INC "School Climate - Not Low Income Students"
label variable engage_NOTLOW_INC "Engagement - Not Low Income Students"
label variable environ_NOTLOW_INC "Environment - Not Low Income Students"
label variable safety_NOTLOW_INC "Safety - Not Low Income Students"
label variable overall_SWD "School Climate - Students with Disabilities"
label variable engage_SWD "Engagement - Students with Disabilities"
label variable environ_SWD "Environment - Students with Disabilities"
label variable safety_SWD "Safety - Students with Disabilities"
label variable overall_NOT_SWD "School Climate - Not Students with Disabilities"
label variable engage_NOT_SWD "Engagement - Not Students with Disabilities"
label variable environ_NOT_SWD "Environment - Not Students with Disabilities"
label variable safety_NOT_SWD "Safety - Not Students with Disabilities"
label variable overall_ECODIS "School Climate - Economically Disadvantaged"
label variable engage_ECODIS "Engagement - Economically Disadvantaged"
label variable environ_ECODIS "Environment - Economically Disadvantaged"
label variable safety_ECODIS "Safety - Economically Disadvantaged"
label variable overall_NOT_ECODIS "School Climate - Not Economically Disadvantaged"
label variable engage_NOT_ECODIS "Engagement - Not Economically Disadvantaged"
label variable environ_NOT_ECODIS "Environment - Not Economically Disadvantaged"
label variable safety_NOT_ECODIS "Safety - Not Economically Disadvantaged"
label variable overall_5 "School Climate - Grade 5"
label variable engage_5 "Engagement - Grade 5"
label variable environ_5 "Environment - Grade 5"
label variable safety_5 "Safety - Grade 5"
label variable overall_8 "School Climate - Grade 8"
label variable engage_8 "Engagement - Grade 8"
label variable environ_8 "Environment - Grade 8"
label variable safety_8 "Safety - Grade 8"
label variable overall_10 "School Climate - Grade 10"
label variable engage_10 "Engagement - Grade 10"
label variable environ_10 "Environment - Grade 10"
label variable safety_10 "Safety - Grade 10"

rename *,lower

save "$saves/VOCAL18-23.dta",replace


